package com.example.study1206.service;

import com.example.study1206.Entity.Attribute;
import com.example.study1206.Entity.InjectionException;
import com.example.study1206.Entity.Requirement;
import com.example.study1206.Entity.Result;
import com.example.study1206.Utils.DataBaseMapper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component("new")
public class NewServiceimp implements MultipleService {
    @Autowired
    DataBaseMapper dataBaseMapper;

    @Override
    public String getByKey(String key) {
        return null;
    }

    @Override
    public String set(String key, String value) {
        return null;
    }

    @Override
    public Boolean isExist(String key) {
        return null;
    }

    @Override
    public Long delete(String key) {
        return null;
    }

    @Override
    public String getAll() {
        return null;
    }

    //    public String getByCondition(Object r){
//        ObjectMapper objectMapper = new ObjectMapper();
//        Requirement requirement = objectMapper.convertValue(r,Requirement.class);
//        List<Map<String,String>> output = topicMapper.bigSelect(requirement.getFormalizedAttributes(),requirement.getTableName(),requirement.getFormalizedConditions());
//        return output.toString();
//    }
    public Object getByConditionByRequirement(Requirement requirement) throws InjectionException {
        String attrbt;
        String cndtn;
        String tblnm;
        String pginf;
        try{
            attrbt = requirement.getFormalizedAttributes();
            cndtn  = requirement.getFormalizedConditions();
            tblnm = requirement.getTableName();
            pginf = requirement.getPageInfo();
        }catch (InjectionException e){
            e.printStackTrace();
            return "非法参数";
        }
        List<Map<String, String>> output;
        try {
            output = dataBaseMapper.bigSelect(attrbt,tblnm,cndtn,pginf);
        }catch (Exception e){
            e.printStackTrace();
            return "查询错误";
        }
        Result result = new Result(output, requirement.getPageNum(), requirement.getPageSize());
        return result;
    }

//    public String getByConditionBySentence(String attributes, String conditions, String tableName, String pageInfo) {
//        List<Map<String, String>> output;
//        if (conditions.equals("") && pageInfo.equals("")) {
//            output = dataBaseMapper.bigSelect(attributes, tableName, conditions, pageInfo);
//        } else if (pageInfo.equals("")) {
//            output = dataBaseMapper.bigSelect(attributes, tableName, "where " + conditions, pageInfo);
//        } else if (conditions.equals("")) {
//            output = dataBaseMapper.bigSelect(attributes, tableName, conditions, "Limit " + pageInfo);
//        } else {
//            output = dataBaseMapper.bigSelect(attributes, tableName, "where " + conditions, "Limit " + pageInfo);
//        }
//        return output.toString();
//    }


    public void uploadFile(InputStream file, String fileName) throws IOException, InjectionException {
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow header = sheet.getRow(0);
        Map<String, Integer> fieldLengthInfo = new HashMap<>();
        for (Cell c : header) {
            fieldLengthInfo.put(getStringCellValue(c), 0);
        }
        String primaryKey = header.getCell(0).toString();
        List<Map<String, String>> dataset = new ArrayList<>();
        int rowNum = sheet.getLastRowNum();
        int columnWidth = header.getLastCellNum();
        for (int i = 1; i <= rowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (row.getLastCellNum() > columnWidth) {
                continue;
            }
            if (row.getCell(0) == null) {
                continue;
            }
            Map<String, String> oneEntity = new HashMap<>();
            for (int j = 0; j < header.getLastCellNum(); j++) {
                Cell c = null;
                if (j <= row.getLastCellNum()) {
                    c = row.getCell(j);
                    if (getStringCellValue(c).length() > fieldLengthInfo.get(getStringCellValue(header.getCell(j)))) {
                        fieldLengthInfo.replace(getStringCellValue(header.getCell(j)), getStringCellValue(c).length());
                    }
                }
                oneEntity.put(getStringCellValue(header.getCell(j)), getStringCellValue(c));
            }

            dataset.add(oneEntity);
        }
        file.close();
        if(sql_inj(fileName)||sql_inj(primaryKey)){
            throw new InjectionException();
        }
        if (dataBaseMapper.findTable(fileName) == null) {
            prepareForUpload1(fieldLengthInfo, fileName, primaryKey);
        } else {
            prepareForUpload2(fieldLengthInfo, fileName);
        }
        putInData(dataset, fileName, primaryKey);

    }

    private void prepareForUpload1(Map<String, Integer> fieldInfo, String fileName, String primaryKey) throws InjectionException{

        String details = "";
        for (String key : fieldInfo.keySet()) {
            if(sql_inj(key)||sql_inj(fieldInfo.get(key).toString())){
                throw new InjectionException();
            }
            details += key + " varchar(" + fieldInfo.get(key) + ") ,\n";
        }

        details += "PRIMARY KEY (" + primaryKey + ")";
        dataBaseMapper.creatTable(fileName, details);

    }

    private void prepareForUpload2(Map<String, Integer> fieldInfo, String fileName) {
        Map<String, String> databaseAttributes = new HashMap<>();
        for (Attribute a : dataBaseMapper.getAttributes(fileName)) {
            databaseAttributes.put(a.getField(), a.getType());
        }
        for (String key : fieldInfo.keySet()) {
            if (databaseAttributes.get(key).contains("varchar")) {
                if (fieldInfo.get(key) > Integer.parseInt(getNumbers(databaseAttributes.get(key)))) {
                    databaseAttributes.replace(key, "varchar(" + fieldInfo.get(key));
                    dataBaseMapper.alterAColumn(fileName, key, fieldInfo.get(key));
                }
            }
        }


    }

    public void putInData(List<Map<String, String>> dataset, String tableName, String primaryKey) throws InjectionException {

//        System.out.println(databaseAttributes);
        for (Map i : dataset) {
            String attributes = "";
            String values = "";
            String conditionInDetails = "";
            String conditionForKey = "";
            for (Object key : i.keySet()) {
                if(sql_inj(key.toString())||sql_inj(i.get(key).toString())){
                    throw new InjectionException();
                }
                attributes += key.toString() + ",";
                values += "'" + i.get(key).toString() + "'" + ",";
//                if (i.get(key).toString().length()>Integer.parseInt(getNumbers(databaseAttributes.get(key)))){
//                    topicMapper.alterAColumn(tableName,key.toString(), String.valueOf(i.get(key).toString().length()));
//                    databaseAttributes.replace(key.toString(),String.valueOf(i.get(key).toString().length()));
//                }
                if (key.toString().equals(primaryKey)) {
                    conditionForKey += key + " = " + "'" + i.get(key).toString() + "'";
                }
                conditionInDetails += key + " = " + "'" + i.get(key).toString() + "'" + " AND ";

            }
            conditionInDetails = conditionInDetails.substring(0, conditionInDetails.length() - 4);
            attributes = attributes.substring(0, attributes.length() - 1);
            values = values.substring(0, values.length() - 1);
//            System.out.println(topicMapper.detect(tableName,conditionInDetails));
//            if(sql_inj(conditionForKey)||sql_inj(conditionInDetails)||sql_inj(attributes)||sql_inj(values)){
//                throw new InjectionException();
//            }
            if (dataBaseMapper.detect(tableName, conditionInDetails).equals("N") && dataBaseMapper.detect(tableName, conditionForKey).equals("Y")) {

                dataBaseMapper.bigDelete(tableName, conditionForKey);
                dataBaseMapper.bigInsert(tableName, attributes, values);

            } else if (dataBaseMapper.detect(tableName, conditionInDetails).equals("N")) {
                dataBaseMapper.bigInsert(tableName, attributes, values);
            }
//            topicMapper.bigInsert(tableName, attributes, values);
//            try {
//                topicMapper.bigInsert(tableName, attributes, values);
//            }catch (Exception e){
//                topicMapper.bigDelete(tableName,condition);
//                topicMapper.bigInsert(tableName, attributes, values);
//            }
        }

    }

    private String getStringCellValue(Cell cell) //获取excel单元格数据内容中为字符串类型的数据
    {
        String strCell = "";
        if (cell == null) {
            return "";

        } else
            //判断数据的类型
            switch (cell.getCellType()) {
                case NUMERIC: //数字
                    DataFormatter dataFormatter = new DataFormatter();

                    strCell = dataFormatter.formatCellValue(cell);
                    //这行代码就可以完美解决数字的小数点显示问题及电话号等长数字的科学计数法显示问题
                    //注意不要使用HSSFDataFormatter方法，这只支持2003版的excel哦
                    break;
                case STRING: //字符串
                    strCell = String.valueOf(cell.getStringCellValue());
                    break;
                case BOOLEAN: //Boolean
                    strCell = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA: //公式
                    strCell = String.valueOf(cell.getCellFormula());
                    break;
                case BLANK: //空值
                    strCell = "";
                    break;
                case ERROR: //故障
                    strCell = "非法字符";
                    break;
                default:
                    strCell = "未知类型";
                    break;
            }
        if (strCell.equals("") || strCell == null) {
            return "";
        }


        return strCell;

    }

    public String getNumbers(String content) {
        Pattern pattern = Pattern.compile("\\d+");
        Matcher matcher = pattern.matcher(content);
        while (matcher.find()) {
            return matcher.group(0);
        }
        return "";
    }
    public static boolean sql_inj(String str){
        //常见可能导致误判的字符组
        String inj_str = "'| and | exec | insert | select | delete | update |"
                + " count |*|%| chr | mid | master | truncate | char | declare |;| or |";
        String inj_stra[] = inj_str.split("\\|");
        //全部转换为小写进行匹配
        String lowStr = str.toLowerCase();
        for (int i=0 ; i < inj_stra.length ; i++ ){
            if (lowStr.indexOf(inj_stra[i]) >= 0){
                return true;
            }
        }
        return false;
    }
}
